package com.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import com.logic.ConstantsValue;
import com.logic.PaymentVO;

public class ServerPaymentsDataBase {
	private Connection connection = null;

	private Statement statement = null;
	private ResultSet resultSet=null;

	private PreparedStatement preparedStatement = null;

	private DataBaseConnection dataBaseConnection = null;

	public ServerPaymentsDataBase() {
		try {
			dataBaseConnection = new DataBaseConnection();
			connection = dataBaseConnection.CallConnection();
			statement = connection.createStatement();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public String PaymentRegistration(String[] request)
	{
		int status=0;
		boolean statusDb=true;
		String result=null;
		String city=null;
		String customerName=null;
		String accountNumber=null;
		String billerName=null;
		String mobileNumber=null;
		try
		{
			
			statement=connection.createStatement();
			resultSet=statement.executeQuery("select * from payments");
			while(resultSet.next())
			{
				if(resultSet.getString("ACCOUNTNUMBER").equalsIgnoreCase(request[3]))
				{
					statusDb=false;
					break;
				}
			}
			if(statusDb)
			{
				preparedStatement=connection.prepareStatement("insert into payments values(?,?,?,?,?)");
				preparedStatement.setString(1,request[3] );
				preparedStatement.setString(2,request[5] );
				preparedStatement.setString(3,request[2] );
				preparedStatement.setString(4,request[4] );
				preparedStatement.setString(5,request[1] );
				status=preparedStatement.executeUpdate();
				if(status==1)
				{
					result="successfully registered with "+request[2];
				}
				else
				{
					result="";
				}
			}
			else
			{
				result="Already Registered with "+request[2];
			}
		
			
		}
		catch (Exception e) {
		e.printStackTrace();
		}
		System.out.println(result);
		return result;
	}
	public String[] paymentSubmit(String[] request)
	{
		boolean status=true;
		boolean accountNum=false;
		String error="";
		String result="";
		String amount=null;
		String balanceDb=null;
		String accountNumber=null;
		String billerName=null;
		String userNumber="";
		try
		{
			accountNumber=request[1];
			billerName=request[3];
			amount=request[2];
			userNumber=request[4];
			//System.out.println("@@@@@@@@@@@@@@@@@");
		//	System.out.println(accountNumber);
			//System.out.println(billerName);
			//System.out.println(userNumber);
			//System.out.println("@@@@@@@@@@@@@@@@@");
			resultSet=statement.executeQuery("select billername from payments where accountnumber='"+accountNumber+"'");
			
			while(resultSet.next())
			{
				accountNum=true;
				if(!billerName.equalsIgnoreCase(resultSet.getString("billername")))
				{
					status=false;
				}
			}
			if(!accountNum)
			{
				error="Account Number Doesnt exist";
				
			}
			else if(!status)
			{
				error="Biller Name Doesnt exist";
			}
			else
			{
				int bal=0,balDb=0,resultUpdate=0;
				resultSet=statement.executeQuery("select BALANCE from ACCOUNT where ACCOUNTNUMBER='"+userNumber+"'");
				while(resultSet.next())
				{
					balanceDb=resultSet.getString("BALANCE");
				}
				bal=Integer.parseInt(amount);
				balDb=Integer.parseInt(balanceDb);
				if(balDb>ConstantsValue.minimunBal&&(balDb-ConstantsValue.minimunBal)>bal)
				{
					balDb=balDb-bal;
					resultUpdate=statement.executeUpdate("update ACCOUNT set BALANCE='"+String.valueOf(balDb)+"' where ACCOUNTNUMBER='"+accountNumber+"'" );
					if(resultUpdate==1)
					{
						result="Amount Transferred";
						//TransAction(String.valueOf(bal),accountNumber);
					}
					
				}
				else
				{
					error="Low Balance";
				}
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		String[] arrayDb=new String[2];
		arrayDb[0]=error;
		arrayDb[1]=result;
		return arrayDb;
	}
	public String ChequePayments(String[] request)
	{
		String accountNumber=request[1];
		String chequeNumber=request[2];
		String amount=request[3];
		String senderNumber=request[4];
		boolean status=false;
		String receiverBalance=new String();
		String result="";
		try
		{
			resultSet=statement.executeQuery("select BALANCE from ACCOUNT where accountnumber='"+accountNumber+"'");
			while(resultSet.next())
			{
				receiverBalance=resultSet.getString("BALANCE");
				status=true;
			}
			if(!status)
			{
				result="Account Number Doesn't Exist";
			}
			if(status)
			{
				int bal=0,balDb=0;
				String balanceDb="";
				resultSet=statement.executeQuery("select * from ACCOUNT where accountnumber='"+senderNumber+"'");
				while(resultSet.next())
				{
					balanceDb=resultSet.getString("BALANCE");
				}
				bal=Integer.parseInt(amount);
				balDb=Integer.parseInt(balanceDb);
				if(balDb>ConstantsValue.minimunBal&&(balDb-ConstantsValue.minimunBal)>bal)
				{
					balDb=balDb-bal;
					int plus=Integer.parseInt(receiverBalance)+bal;
					statement.executeUpdate("update ACCOUNT set BALANCE='"+String.valueOf(balDb)+"' ,ISSUEDCHEQUEAMT='"+String.valueOf(bal)+"', CHEQUENUM='"+chequeNumber+"' , CHEQUESTATUS='ISSUED' where ACCOUNTNUMBER='"+senderNumber+"'" );
					statement.executeUpdate("update ACCOUNT set BALANCE='"+String.valueOf(plus)+"' , DEPOSITEDCHEQUEAMT='"+String.valueOf(bal)+"', CHEQUENUM='"+chequeNumber+"' , CHEQUESTATUS='DEPOSITED' where ACCOUNTNUMBER='"+accountNumber+"'" );
					result="cheque Transferred and balance is "+balDb;
				}
				else
				{
					result="Too low Balance to Transfer";
				}
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return result;
	}

}
